﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using System.Data;
using System.Configuration;

namespace qdata
{
    public   class GetCarStation
    {


        public void SaveCarstation(string strval,string url)
        {

            List<string> sum = new List<string>();
            string str = strval;
            var s1 = Regex.Matches(str, @"(?is)(<tr[^>]*>)(.*?)(</tr>)");
            int i = 0;
            string sql = " insert into Carbanci (StationName,EStation,Otherstation,Lichen,Fdate) ";
            foreach (Match m1 in s1)
            {
                string svalue = "values('{0}','{1}','{2}','{3}','{4}') ";
                if (i != 0)
                {
                    var s2 = Regex.Matches(m1.Value, @"(?is)(<td[^>]*>)(.*?)(</td>)");

                    int t = 0;
                    foreach (Match m2 in s2)
                    {
                        if (t == 0)
                        {
                            string surl =url+ GetUrl(m2.Groups[2].Value);
                            Common h = new Common();
                            string stationHtml = Getstation(h.GetHtmlCode(surl)); 


                        }

                        string temp = Getstr(m2.Groups[2].Value);
                        svalue = svalue.Replace("{" + t.ToString() + "}", temp);
                        t++;
                    }
                    svalue = sql + svalue;
                    ExecuteDataTable(svalue);
                }

                i++;
            }
        }

        public string Getstation(string html)
        {
            string pattern = @"(?is)(<span id=""lb_Intro"">)(.*?)(</span>)";
            var s2 = Regex.Matches(html, pattern);
            string c = s2[0].Value;
            return c;

        }

        private string GetUrl(string v)
        {
           string  pattern = @"(?is)(?<=href\s*=\s*(['""]?)\s*)[^'""\s>]+(?=\s*\1)";
            var s2 = Regex.Matches(v, pattern);

            string c=s2[0].Value;
            return c;
        }




        private string Getstr(string s)
        {
            Regex re = new Regex("</?.+?>");
            string result = re.Replace(s, "");
            return result;
        }


        string connectionString = ConfigurationSettings.AppSettings["connectionstring"].ToString();
        /// <summary>
        /// 执行SQL,返回DataTable
        /// </summary>
        /// <param name="Sql">SQL</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string Sql)
        {

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                try
                {
                    string sqlCommand = Sql;
                    using (OleDbCommand cmd = new OleDbCommand(sqlCommand, connection))
                    {
                        connection.Open();
                        OleDbDataAdapter adp = new OleDbDataAdapter(sqlCommand, connection);
                        adp.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adp.Fill(ds);
                        return ds.Tables[0];
                    }
                }
                catch (Exception ex)
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    return null;
                }
            }
        }

    }
}
